This quite often-used method requires use of scripts because total value in an ordinary report becomes available only after all group's records are handled. To display a sum in the group's header (before the group is handled), the following algorithm is used:
- the two-pass option of the report is turned on ("Report|Options..." menu item);
- in the first pass, the sum of each group is calculated and saved in an array;
- in the second pass, the values are extracted from the array and typed in the group's header.
Let us show, two ways of how this task may be accomplished. First of all, let us create a new project in Delphi, put the "TQuery," "TfrxReport," and "TfrxDBDataSet" components to the form. Set them in the following way:
Query1:
DatabaseName = 'DBDEMOS'
SQL =
select * from customer, orders
where orders.CustNo = customer.CustNo
order by customer.CustNo, orders.OrderNo
frxDBDataSet1:
DataSet = Query1
UserName = 'Group'
Enter the designer and connect our data source to the report. Enable the double pass in report's settings (the "Report|Options..." menu item). Add two bands to the report: "Group header" and "Master data." In the "Group header" band's editor, specify the condition ("Group.CustNo" data field). Connect the data-band to the "Group" data source, and then arrange objects in the following way:
For entering sum value, we use the selected object in the picture (in our example its name is "Memo8").
The first way.
We use the "TStringList" class as an array for sums' storage. Store values in the form of lines. At the same time, the first line in the list corresponds to the value of the first group, etc. The integer-valued variable (which we will augment after printing the next group) is used for calculating the group's number.
Thus, our script will look as follows:
var
List: TStringList;
i: Integer;
procedure frReport1OnStartReport(Sender: TfrxComponent);
begin
List := TStringList.Create;
end;
procedure frReport1OnStopReport(Sender: TfrxComponent);
begin
List.Free;
end;
procedure Page1OnBeforePrint(Sender: TfrxComponent);
begin
i := 0;
end;
procedure GroupHeader1OnBeforePrint(Sender: TfrxComponent);
begin
if Engine.FinalPass then
Memo8.Text := 'Sum: ' + List[i];
end;
procedure GroupFooter1OnBeforePrint(Sender: TfrxComponent);
begin
List.Add(FloatToStr(<SUM(<Group."ItemsTotal">,MasterData1)>));
Inc(i);
end;
begin
end.
Looking at the names of the procedures, you can easily find out the events we have used. They are: "Report.OnStartReport," "Report.OnStopReport," "Page1.OnBeforePrint," "GroupHeader1.OnBeforePrint," and "GroupFooter1.OnBeforePrint." As for the first two events, they are called, as it was said, in the beginning and in the end of the report respectively. To create handlers for these events, one should select the "Report" object in the "Report tree" window; its properties will appear in the objects' inspector. After that, we would act in a standard way: switch to the inspector's "Events" bookmark and create handlers.
Why didn't we use the main procedure for creation of the "List" list and performed it in the "OnStartReport" event? That is because the created object should be cleared after a report is finished. That is why it is rather logical to create objects in the "OnStartReport" event and clear them via the "OnStopReport." In other cases (when memory does not need to be emptied) one can use the main procedure for initialization of variables.
Everything concerning creation and clearing of the "List" object seems to be quite obvious. Now let us examine the work of the script. In the beginning of the page, the counter of the current group (the "i" variable) is reset to "0" and increments after printing each group (in the "GroupFooter1.OnBeforePrint" event). The calculated sum's value is added to the list in this very event. The "GroupHeader1.OnBeforePrint" event does not trigger during the first pass (the "Engine.FinalPass" verification). During the second pass (when the "List" list is filled with values), the value, which corresponds to the current group is retrieved into this event, and it is recorded to the "Memo8" object's text, which displays the sum total in the group title. In a finished report, it looks in the following way:
As we can see, the algorithm is rather simple. Nevertheless, it can be simplified.
The second way.
We use the list of report's variables as an array for sums' storage. As we remember, reference to such objects is performed via the "Get" and "Set" functions. That saves us from difficulty to create superfluous objects and to free the storage. Our script will look as follows:
procedure GroupHeader1OnBeforePrint(Sender: TfrxComponent);
begin
if Engine.FinalPass then
Memo8.Text := 'Sum: ' + Get(<Group."CustNo">);
end;
procedure GroupFooter1OnBeforePrint(Sender: TfrxComponent);
begin
Set(<Group."CustNo">,
FloatToStr(<SUM(<Group."ItemsTotal">,MasterData1)>));
end;
begin
end.
As you can see, the script was rather simplified. A code in the "GroupFooter1.OnBeforePrint" handler sets a variable's value with a name similar to the client's number (one can use any identifier, which unambiguously identifies the client, for example, his name